using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core;
using IglooCoder.Commons.WcfNhibernate;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Linq;

namespace Infrastucture
{
    public class MutasiStokRepository : BaseNHibernateRepository<MutasiStok>
    {
        private int i = 0;
        public MutasiStokRepository(ISessionStorage sessionStorage)
            : base(sessionStorage)
        {
            iwRepository = new InventoryWarehouseRepository(sessionStorage);
        }

        public MutasiStokRepository(ISessionStorage sessionStorage, ISession session)
            : base(sessionStorage, session)
        {
            iwRepository = new InventoryWarehouseRepository(sessionStorage, session);
        }

        private InventoryWarehouseRepository iwRepository;
        public void DeleteMutasiStok(string ReferenceNumber)
        {
         
            ISQLQuery command =
                Session.CreateSQLQuery("Delete from MutasiStok Where ReferenceNumber='" + ReferenceNumber + "'");
            int res = command.ExecuteUpdate();
        }

        public void DeleteMutasiStok(string ReferenceNumber, string SerialNumber)
        {         
            ISQLQuery command =
                Session.CreateSQLQuery("Delete from MutasiStok Where ReferenceNumber='" + ReferenceNumber +
                                       "' AND SerialNumber='" + SerialNumber + "'");
            int res = command.ExecuteUpdate();
        }

        public MutasiStok FindLastMutasi(string serialNumber, string KodeWarehouse)
        {
            MutasiStok MutasiStok;
            MutasiStok =
                Session.Linq<MutasiStok>().Where(x => x.SerialNumber == serialNumber && x.KodeWarehouse == KodeWarehouse)
                    .OrderByDescending(
                        x => x.TglMutasi).FirstOrDefault();
            if (MutasiStok == null)
                MutasiStok = new MutasiStok();
            return MutasiStok;
        }

        public MutasiStok FindLastMutasi(string serialNumber, string KodeWarehouse,DateTime tglMutasi)
        {
            MutasiStok MutasiStok;
            //DateTime batasWaktu = new DateTime(tglMutasi.Year,tglMutasi.Month,tglMutasi.Day,23,59,0,0);
            MutasiStok =
                Session.Linq<MutasiStok>().Where(x => x.SerialNumber == serialNumber && x.KodeWarehouse == KodeWarehouse && x.TglMutasi <= tglMutasi)
                    .OrderByDescending(
                        x => x.TglMutasi).FirstOrDefault();
            if (MutasiStok == null)
                MutasiStok = new MutasiStok();
            return MutasiStok;
        }

        public void SaveOrUpdate(MutasiStok mutasiStok,string KodePallet)
        {
            mutasiStok.Pallet = KodePallet;
            
            SaveOrUpdate(mutasiStok, ref i);
            //Jika transaksi mundur atau tgltransaksi < tgl saat ini
            if (mutasiStok.TglMutasi.Date < DateTime.Now.Date)
            {
                var mutasiListSesudah =
                    Session.Linq<MutasiStok>().Where(
                        x => x.SerialNumber == mutasiStok.SerialNumber && x.KodeWarehouse == mutasiStok.KodeWarehouse &&
                             x.TglMutasi > mutasiStok.TglMutasi).OrderBy(x=>x.TglMutasi).ToList();

                Decimal QtySisa = 0;
                Decimal SheetSisa = 0;
                foreach (var stok in mutasiListSesudah)
                {
                    if (QtySisa == 0)
                    {
                        stok.QtySisa = mutasiStok.QtySisa - stok.QtyKeluar + stok.QtyMasuk;
                        stok.SheetSisa = mutasiStok.SheetSisa - stok.SheetKeluar + stok.SheetMasuk;
                    }else
                    {
                        stok.QtySisa = QtySisa - stok.QtyKeluar + stok.QtyMasuk;
                        stok.SheetSisa = SheetSisa - stok.SheetKeluar + stok.SheetMasuk;
                    }
                    
                    //validasi
                    if (stok.QtySisa < 0)
                        throw  new Exception("Transaksi tidak diperbolehkan karena Stok pada tanggal " + stok.TglMutasi.ToString("dd-MM-yyyy") + " menjadi minus");
                    SaveOrUpdate(stok,ref i);
                    QtySisa = stok.QtySisa;
                    SheetSisa = stok.SheetSisa;
                }

                var lastMutasi = mutasiListSesudah.LastOrDefault();
                if (lastMutasi == null)
                    lastMutasi = mutasiStok;
            }

        }


        public void AdjustStokBySystem()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Select m1.* from MutasiStok m1  ");
            sb.Append(" INNER JOIN ");
            sb.Append(" ( ");
	        sb.Append(" Select * from MutasiStok where TYPE<>'SaldoAwal' ");
            sb.Append(" ) m2 ON m1.SerialNumber = m2.SerialNumber AND m1.KodeWarehouse = m2.KodeWarehouse ");
            sb.Append(" where m1.Type='SaldoAwal' and m1.TglMutasi<'2011-02-01' ");
            var mutasiStokQuery = Session.CreateSQLQuery(sb.ToString());
            var mutasiStokSaldoAwals = mutasiStokQuery.AddEntity(typeof(MutasiStok)).List<MutasiStok>();

            sb.Clear();
            sb = new StringBuilder();
            sb.Append(" SELECT m.* FROM mutasiStok m ");
            sb.Append(" INNER JOIN  ");
            sb.Append(" ( ");
		    sb.Append(" Select m1.* from MutasiStok m1  ");
		    sb.Append(" INNER JOIN  ");
		    sb.Append(" ( ");
			sb.Append("     Select * from MutasiStok where TYPE<>'SaldoAwal' ");
		    sb.Append(" ) m2 ON m1.SerialNumber = m2.SerialNumber AND m1.KodeWarehouse = m2.KodeWarehouse ");
		    sb.Append(" where m1.Type='SaldoAwal' and m1.TglMutasi<'2011-02-01'  ");
            sb.Append(" ) m3 ON m.SerialNumber = m3.SerialNumber AND m.KodeWarehouse=m3.KodeWarehouse AND m.Type<>'SaldoAwal' AND m.Type<>'MutasiWarehouse' ");
            //sb.Append(" ORDER BY m.SerialNumber,m.TglMutasi,m.KodeWarehouse ");
            var mutasiStokRepairs = Session.CreateSQLQuery(sb.ToString()).AddEntity(typeof(MutasiStok)).List<MutasiStok>();


            foreach (var mutasiStokRepair in mutasiStokRepairs)
            {
                var mutasiStokSaldoAwal = mutasiStokSaldoAwals.FirstOrDefault(
                        x =>
                        x.SerialNumber == mutasiStokRepair.SerialNumber &&
                        x.KodeWarehouse == mutasiStokRepair.KodeWarehouse);

                mutasiStokRepair.QtySisa -= mutasiStokSaldoAwal.QtySisa;
                mutasiStokRepair.SheetSisa -= mutasiStokSaldoAwal.SheetSisa;
               
                var inventoryWarehouse =
                    iwRepository.GetDataOne(
                        x =>
                        x.SerialNumber == mutasiStokRepair.SerialNumber &&
                        x.KodeWarehouse == mutasiStokRepair.KodeWarehouse);

                this.SaveOrUpdate(mutasiStokRepair);
                inventoryWarehouse.Sheet = mutasiStokRepair.SheetSisa;
                inventoryWarehouse.Qty = mutasiStokRepair.QtySisa;
                //iwRepository.SaveOrUpdate(inventoryWarehouse);

                //if (mutasiStokRepair.Type=="MutasiWarehouse")
                //{
                //    var mutasiStokLawannya =
                //       Session.Linq<MutasiStok>().Where(x => x.SerialNumber == mutasiStokRepair.SerialNumber &&
                //                                             x.KodeWarehouse != mutasiStokRepair.KodeWarehouse &&
                //                                              x.Type == "MutasiWarehouse").FirstOrDefault();

                //   inventoryWarehouse =
                //   iwRepository.GetDataOne(
                //       x =>
                //       x.SerialNumber == mutasiStokLawannya.SerialNumber &&
                //       x.KodeWarehouse == mutasiStokLawannya.KodeWarehouse);

                //   mutasiStokLawannya.QtySisa -= mutasiStokSaldoAwal.QtySisa;
                //   mutasiStokLawannya.SheetSisa -= mutasiStokSaldoAwal.SheetSisa;
                //   this.SaveOrUpdate(mutasiStokLawannya);

                //    if (inventoryWarehouse != null)
                //    {
                //        inventoryWarehouse.Sheet = mutasiStokLawannya.SheetSisa;
                //        inventoryWarehouse.Qty = mutasiStokLawannya.QtySisa;
                //        iwRepository.SaveOrUpdate(inventoryWarehouse);
                //    }
                    
                //}
            }

            ISQLQuery command =
            Session.CreateSQLQuery("Delete FROM MutasiStok  where Type='SaldoAwal' and TglMutasi<'2011-02-01'");
            int res = command.ExecuteUpdate();
           

        }


        public IList<MutasiStok> FindLastMutasiByBTBElse(String BTBELseNumber)
        {
            var query =
                Session.CreateSQLQuery("SELECT * FROM MutasiStok ums WHERE ums.SerialNumber IN " +
                                       "( SELECT NoLog FROM BTBElseDetail bd WHERE bd.NoBtb='" + BTBELseNumber + "')");
            return query.AddEntity(typeof (MutasiStok)).List<MutasiStok>();
        }


        public MutasiStok FindLastMutasiInList(IList<MutasiStok> mutasiStoks,string SerialNumber, string KodeWarehouse, DateTime tglMutasi)
        {
            MutasiStok MutasiStok = mutasiStoks.Where(x => x.SerialNumber == SerialNumber && x.KodeWarehouse == KodeWarehouse && x.TglMutasi <= tglMutasi)
                .OrderByDescending(
                    x => x.TglMutasi).FirstOrDefault();
            if (MutasiStok == null)
                MutasiStok = new MutasiStok();
            return MutasiStok;
        }


        public IList<MutasiStok> FindLastMutasiCollection(IList<BtbElseDetail> btbElseDetails)
        {
            IEnumerable<MutasiStok> mutasiStoks = Enumerable.Empty<MutasiStok>();
            foreach (var btbElseDetail in btbElseDetails)
            {
                var criteria = Session.CreateCriteria(typeof (MutasiStok));
                var expression = Expression.Conjunction();
                expression.Add(Expression.Eq("SerialNumber", btbElseDetail.NoLog));
                expression.Add(Expression.Eq("KodeWarehouse", btbElseDetail.Warehouse.KodeWarehouse));
                expression.Add(Expression.Le("TglMutasi", btbElseDetail.Btb.TglBtb));
                criteria.Add(expression).AddOrder(Order.Desc("TglMutasi")).SetFirstResult(1).SetMaxResults(1);
                mutasiStoks =  mutasiStoks.Concat(criteria.Future<MutasiStok>());
            }


            return mutasiStoks.ToList();

        }


        public Decimal GetQtyOnHand(string serialNumber, DateTime tglMutasi)
        {
            var MutasiStoks =
                Session.Linq<MutasiStok>().Where(x => x.SerialNumber == serialNumber && x.TglMutasi < tglMutasi);
            return MutasiStoks.Sum(x => x.QtyMasuk) - MutasiStoks.Sum(x => x.QtyKeluar);
        }

        public  void SaveOrUpdate(MutasiStok mutasiStok,ref int increment)
        {
            increment += 1;
            mutasiStok.TglMutasi = mutasiStok.TglMutasi.AddSeconds(increment);
            base.SaveOrUpdate(mutasiStok);
        }
    }
}